/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

import java.sql.*;
import java.util.ArrayList;
import Model.*;
import java.util.Collections;
import java.util.Comparator;

/**
 *
 * @author Eduardo
 */
public class Query {

    public Statement conn() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/delc", "root", "password");
        Statement stmt = conn.createStatement();
        return stmt;
    }

    public Professor getProf(String id) throws SQLException, ClassNotFoundException {
        String query;
        //if (id == null) query = "SELECT id, siape, nome, cargahoraria, email FROM professores";
        query = "SELECT id, siape, nome, cargahoraria, email FROM professores WHERE id = " + id;
        ResultSet rs = conn().executeQuery(query);
        Professor prof = new Professor();

        while (rs.next()) {
            prof.setId(rs.getInt("id"));
            prof.setNome(rs.getString("nome"));
            prof.setSiape(rs.getInt("siape"));
            prof.setEmail(rs.getString("email"));
            prof.setDedicacao(rs.getString("cargahoraria"));

        }
        return prof;
    }

    public ArrayList<Professor> getListaProf() throws SQLException, ClassNotFoundException {
        String query;
        //if (id == null) query = "SELECT id, siape, nome, cargahoraria, email FROM professores";
        query = "SELECT id, siape, nome, cargahoraria, email, status FROM professores";
        ResultSet rs = conn().executeQuery(query);
        Professor prof;
        ArrayList<Professor> listaProf = new ArrayList<Professor>();
        while (rs.next()) {
            prof = new Professor();
            prof.setId(rs.getInt("id"));
            prof.setStatus(rs.getInt("status"));
            prof.setNome(rs.getString("nome"));
            prof.setSiape(rs.getInt("siape"));
            prof.setEmail(rs.getString("email"));
            prof.setDedicacao(rs.getString("cargahoraria"));
            listaProf.add(prof);
        }

        return listaProf;


    }

    public Disciplina getDisc(String cod) throws SQLException, ClassNotFoundException {
        String query;
        query = "SELECT * FROM disciplinas WHERE id = " + cod;
        ResultSet rs = conn().executeQuery(query);
        Disciplina disc = new Disciplina();

        while (rs.next()) {
            disc.setId(rs.getInt("id"));
            disc.setCod(rs.getString("cod"));
            disc.setNome(rs.getString("nome"));
            disc.setCargahoraria(rs.getInt("cargahoraria"));
            disc.setCredito(rs.getInt("creditos"));
            disc.setResumo(rs.getString("resumo"));
        }
        return disc;
    }

    public ArrayList<Disciplina> getListaDisc() throws SQLException, ClassNotFoundException {
        String query;
        query = "SELECT * FROM disciplinas ORDER BY status DESC";
        ResultSet rs = conn().executeQuery(query);
        Disciplina disc;
        ArrayList<Disciplina> listaDisc = new ArrayList<Disciplina>();

        while (rs.next()) {
            disc = new Disciplina();
            disc.setId(rs.getInt("id"));
            disc.setCod(rs.getString("cod"));
            disc.setNome(rs.getString("nome"));
            disc.setCargahoraria(rs.getInt("cargahoraria"));
            disc.setCredito(rs.getInt("creditos"));
            disc.setResumo(rs.getString("resumo"));
            disc.setStatus(rs.getInt("status"));
            listaDisc.add(disc);
        }
        return listaDisc;
    }

    public ResultSet getPref(String id) throws SQLException, ClassNotFoundException {
        String query;
        query = "SELECT * FROM preferencias WHERE professor_id = " + id;
        ResultSet rs = conn().executeQuery(query);
        return rs;
    }

    public ResultSet getGrade(String id) throws SQLException, ClassNotFoundException {
        String query;
        query = "select g.disciplina_id, g.sem, g.curso_id, c.nome, d.id, d.nome, d.cod, d.cargahoraria from grade g left join disciplinas d on g.disciplina_id = d.id right join cursos c on c.curso_id = g.curso_id where g.curso_id = " + id;
        ResultSet rs = conn().executeQuery(query);
        return rs;
    }

    public ResultSet getDiscSem(String id) throws SQLException, ClassNotFoundException {
        String query;
        query = "select g.disciplina_id, g.sem, g.curso_id, c.nome, d.id, d.nome, d.cod, d.cargahoraria from grade g left join disciplinas d on g.disciplina_id = d.id right join cursos c on c.curso_id = g.curso_id where g.curso_id = " + id;
        ResultSet rs = conn().executeQuery(query);
        return rs;
    }

    public ResultSet getSolicitacoes(String id) throws SQLException, ClassNotFoundException {
        String query;
        if (id == null) {
            query = "select * from oferta where condicao = 1";
        } else {
            query = "select * from oferta where condicao = 1 and id = " + id;
        }
        ResultSet rs = conn().executeQuery(query);
        return rs;
    }

    public ResultSet getSolicitacoesTable() throws SQLException, ClassNotFoundException {
        String query;
        query = "select o.id, o.id_professor, o.id_sala, o.id_curso, o.id_disciplina, o.hora1, o.hora2, o.hora3, o.dia1, o.dia2, o.dia3, o.horaaula1, o.horaaula2, o.horaaula3, o.condicao, d.nome as disciplina, c.nome as curso  from oferta o left join cursos c on o.id_curso = c.curso_id right join disciplinas d on d.id = o.id_disciplina where condicao = 1";
        ResultSet rs = conn().executeQuery(query);
        return rs;
    }
    public ResultSet getTurmasTable() throws SQLException, ClassNotFoundException {
        String query;
        query = "select o.id, o.id_professor, o.id_sala, o.id_curso, o.id_disciplina, o.hora1, o.hora2, o.hora3, o.dia1, o.dia2, o.dia3, o.horaaula1, o.horaaula2, o.horaaula3, o.condicao, d.nome as disciplina, c.nome as curso, s.numero as sala  from oferta o left join cursos c on o.id_curso = c.curso_id right join disciplinas d on d.id = o.id_disciplina inner join sala s on s.id = o.id_sala where condicao > 1";
        ResultSet rs = conn().executeQuery(query);
        return rs;
    }
    public ArrayList<Preferencias> getProfessorDisc(Integer discID) throws SQLException, ClassNotFoundException {
        String query;
        query = "select id, nome from professores";
        ResultSet rs = conn().executeQuery(query);
        query = "select pf.professor_id, pf.disc_1, pf.disc_2, disc_3, p.nome from preferencias pf inner join professores p on pf.professor_id = p.id";
        ResultSet rs2 = conn().executeQuery(query);
        ArrayList<Preferencias> professores = new ArrayList<>();
        Preferencias pref;
       

        while (rs2.next()) {
            pref = new Preferencias();
            pref.setId(rs2.getInt("professor_id"));
            pref.setNome(rs2.getString("nome"));

            if (rs2.getInt("disc_1") == discID) {
                pref.setPontos(3);
            }
            else if (rs2.getInt("disc_2") == discID) {
                pref.setPontos(2);
            }
            else if (rs2.getInt("disc_3") == discID) {
                pref.setPontos(1);
            }
            else {
                pref.setPontos(0);
            }
            professores.add(pref);
        }

        if (professores.size() > 0) {
            Collections.sort(professores, new Comparator<Preferencias>() {
                @Override
                public int compare(final Preferencias object1, final Preferencias object2) {
                    return object1.getPontos().compareTo(object1.getPontos());
                }
            });
        }
        return professores;
    }
    public ResultSet getSalas() throws SQLException, ClassNotFoundException {
        String query;
        query = "select * from sala";
        ResultSet rs = conn().executeQuery(query);
        return rs;
    }
    public ResultSet getTurmas(String id) throws SQLException, ClassNotFoundException {
        String query;
        if (id != null) query = "select o.id, o.id_professor, o.id_sala, o.id_curso, o.id_disciplina, o.hora1, o.hora2, o.hora3, o.dia1, o.dia2, o.dia3, o.horaaula1, o.horaaula2, o.horaaula3, o.condicao, d.nome as disciplina, c.nome as curso, s.numero as sala  from oferta o left join cursos c on o.id_curso = c.curso_id right join disciplinas d on d.id = o.id_disciplina inner join sala s on s.id = o.id_sala where id_professor = " + id +" and condicao > 1";
        else query = "select o.id, o.id_professor, o.id_sala, o.id_curso, o.id_disciplina, o.hora1, o.hora2, o.hora3, o.dia1, o.dia2, o.dia3, o.horaaula1, o.horaaula2, o.horaaula3, o.condicao, d.nome as disciplina, c.nome as curso, s.numero as sala  from oferta o left join cursos c on o.id_curso = c.curso_id right join disciplinas d on d.id = o.id_disciplina inner join sala s on s.id = o.id_sala where and condicao > 1";
        ResultSet rs = conn().executeQuery(query);
        return rs;
    }
}
